

'''
-- 设备健康管理页面 视频配置表
CREATE TABLE video_resources (

    id SERIAL PRIMARY KEY,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    data_json JSONB NOT NULL
);

SELECT SETVAL('video_resources_id_seq',max(id),TRUE) FROM video_resources;


-- 视频配置和设备关联表
CREATE TABLE video_resources_device (

    id SERIAL PRIMARY KEY,
    device_id INTEGER NOT NULL,
    video_resources_id INTEGER NOT NULL,
    CONSTRAINT fk_video_resources_id
        FOREIGN KEY (video_resources_id)
        REFERENCES video_resources(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_device_id
        FOREIGN KEY (device_id)
        REFERENCES devices(id)
        ON DELETE CASCADE,

    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SELECT SETVAL('video_resources_device_id_seq',max(id),TRUE) FROM video_resources_device;
'''

from . import logger

class VideoResourcesTable:

    def __init__(self, connection, cursor):

        self.connection = connection
        self.cursor = cursor

    # 创建视频配置
    def create_video_resources(self, data_json):

        try:
            self.cursor.execute("INSERT INTO video_resources (data_json) VALUES (%s) RETURNING id", (data_json,))

        except Exception as e:
            logger.error("Error creating video resources: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources created successfully.")
            return True, self.cursor.fetchone()["id"]
        
    # 根据id获取视频配置
    def get_video_resources_by_id(self, vs_id):
    
        try:
            self.cursor.execute("SELECT * FROM video_resources WHERE id = %s", (vs_id,))
            video_resources = self.cursor.fetchone()
        except Exception as e:
            logger.error("Error getting video resources by id: {}".format(e))
            video_resources = None
        else:
            logger.info(f"Video resources {vs_id} retrieved successfully.")
        return video_resources
    
    # 获取最新视频配置
    def get_latest_video_resources(self):
        
        try:
            self.cursor.execute("SELECT * FROM video_resources ORDER BY id DESC LIMIT 1")
            video_resources = self.cursor.fetchone()
        except Exception as e:
            logger.error("Error getting latest video resources: {}".format(e))
            video_resources = None
        else:
            logger.info(f"Latest video resources retrieved successfully.")
        return video_resources
    
    # 获取所有视频配置
    def get_all_video_resources(self, offset=0, limit=10):
        
        try:
            self.cursor.execute("SELECT * FROM video_resources ORDER BY id DESC LIMIT %s OFFSET %s", (limit, offset))
            video_resources = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting all video resources: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"All video resources retrieved successfully.")

            return True, video_resources
    
    # 删除视频配置
    def delete_video_resources(self, vs_id):
        
        try:
            self.cursor.execute("DELETE FROM video_resources WHERE id = %s", (vs_id,))
            self.connection.commit()

        except Exception as e:
            logger.error("Error deleting video resources: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources {vs_id} deleted successfully.")
            return True, None
        

    # 创建设备和视频配置关联
    def create_video_resources_device_relation(self, device_id, video_resources_id):
        
        try:
            self.cursor.execute("INSERT INTO video_resources_device (device_id, video_resources_id) VALUES (%s, %s)", (device_id, video_resources_id))
            self.connection.commit()

        except Exception as e:
            logger.error("Error creating video resources device relation: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources device relation created successfully.")
            return True, None
        
    # 批量创建设备和视频配置关联
    def create_video_resources_device_relations(self, device_ids, video_resources_id):
        
        try:
            self.cursor.executemany("INSERT INTO video_resources_device (device_id, video_resources_id) VALUES (%s, %s)", [(device_id, video_resources_id) for device_id in device_ids])

        except Exception as e:
            logger.error("Error creating video resources device relations: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources device relations created successfully.")
            return True, None
    
    # 根据设备id获取视频配置
    def get_video_resources_by_device_id(self, device_id):
        
        try:
            self.cursor.execute("SELECT * FROM video_resources_device order by id DESC WHERE device_id = %s LIMIT 1", (device_id,))
            video_resources_device = self.cursor.fetchone()

        except Exception as e:
            logger.error("Error getting video resources by device id: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources device {device_id} retrieved successfully.")
            return True, video_resources_device
        
    # 根据视频配置id获取设备
    def get_device_by_video_resources_id(self, video_resources_id):
        
        try:
            self.cursor.execute("SELECT * FROM video_resources_device WHERE video_resources_id = %s", (video_resources_id,))
            video_resources_device = self.cursor.fetchall()

        except Exception as e:
            logger.error("Error getting device by video resources id: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Device by video resources {video_resources_id} retrieved successfully.")
            return True, video_resources_device
    
    # 删除设备和视频配置关联
    def delete_video_resources_device_relation(self, device_id, video_resources_id):
        
        try:
            self.cursor.execute("DELETE FROM video_resources_device WHERE device_id = %s AND video_resources_id = %s", (device_id, video_resources_id))
            self.connection.commit()

        except Exception as e:
            logger.error("Error deleting video resources device relation: {}".format(e))
            return False, str(e)

        else:
            logger.info(f"Video resources device relation deleted successfully.")
            return True, None